<?php
/*
 * File Name: DatabaseHandler.php
 * Date: November 15, 2011
 * Author: Eduardo Abi Zeid
 * Description: Contiene conexion a la bd, 
 *				y funcionalidad para hacer operaciones crud
 *
 *              Todas las funciones devuelven trueturn true
 *              si terminaron exitosamente, en caso contrario false
 *
 */

include_once '../classes/ResultadoPaginado.php';
include_once '../classes/ReflectUtils.php';

class DatabaseHandler
{

    private $db_host = 'gastonserver.dyndns.org';     
    private $db_user = 'tv-salud';         
    private $db_pass = 'tv-salud';          
    private $db_name = 'tv-salud';          
    

    private $con = false;               // Check para ver si la conexion esta activa
    private $result = array();          // Resultados que son devueltos por la query
    private $numResults = 0;

    
	public function __autoload($class_name) {
		    include_once '../classes/'. $class_name . '.php';
	}
    
    /*
     * Conecta a la base, trabaja como singleton
     */
    public function connect()
    {
        if($this->con){
        	return true;        	
        }
        
		$myconn = @mysql_connect($this->db_host,$this->db_user,$this->db_pass);
		if($myconn)
		{
			mysql_query("SET NAMES 'utf8'");
			$this->con = @mysql_select_db($this->db_name,$myconn);
			return $this->con;
		}else
        {
			return false;
		}
        
    }

    /*
    * Changes the new database, sets all current results
    * to null
    */
    public function setDatabase($name)
    {
        if($this->con)
        {
            if(@mysql_close())
            {
                $this->con = false;
                $this->results = null;
                $this->db_name = $name;
                $this->connect();
            }
        }

    }

    /*
    * Chequea si la tabla existe
    */
    private function tableExists($table)
    {
        $tablesInDb = @mysql_query('SHOW TABLES FROM '.$this->db_name.' LIKE "'.$table.'"');
        if($tablesInDb)
        {
            return mysql_num_rows($tablesInDb)==1;
        }
    }

    /*
    * Implementacion de la funcionalidad select
    * Required: table (el nmbre de la tabla)
    * Optional: rows (columnas-projections- separadas por coma)
    *           where (columna = valor)
    *           order (columna + direccion)
    */
    public function select($table, $rows = '*', $where = null, $order = null, $page = null,$page_size = 20)
    {
        $this->cleanResults();
    	$q = 'SELECT '.$rows.' FROM '.$table;
        if(!empty($where))
            $q .= ' WHERE '.$where;
        if($order != null)
            $q .= ' ORDER BY '.$order;
		
        if($page != null){
        	$q .= ' LIMIT ' . ($page_size * ($page -1)) . ',' . $page_size; 
        }
        $query = @mysql_query($q);
        if($query)
        {
        	$this->numResults = mysql_num_rows($query);
            for($i =0;  $row = mysql_fetch_assoc($query);$i++) {
            	$this->result[$i] = $row;
            }
            
            return true;
        }
        else
        {
            return false;
        }
    }
    
    public function count_select($table,$where = null){
    	$q = 'SELECT count(*) as CNT FROM '. $table;
    	if(!empty($where))
            $q .= ' WHERE '.$where;
            
        $query = @mysql_query($q);
        
        if($query){
         	for($i =0;  $row = mysql_fetch_assoc($query);$i++) {
            	$this->result[$i] = $row;
            }
            return true;
        }else{
        	return false;
        }
    }
    
    public function select_paginado($table, $rows = '*', $where = null, $order = null, $page = 1,$page_size = 20){
    	$this->select($table, $rows , $where, $order , $page,$page_size);
    	$resultado_paginado = new ResultadoPaginado();
    	$resultado_paginado->setResults($this->result);
    	$this->count_select($table,$where);
    	$resultado_paginado->setCount(ceil($this->result[0]['CNT']/$page_size));
    	$resultado_paginado->setPage($page);
    	$this->result = $resultado_paginado; 
    	return true;
    }
    
    public function free_select_paginado($query, $page = 1,$page_size = 20){
    	
    	$resultado_paginado = new ResultadoPaginado();
    	$this->count_free_select($query);
    	$resultado_paginado->setCount(ceil($this->result[0]['CNT']/$page_size));
    	
    	if($page != null){
    		$query .= ' LIMIT ' . ($page_size * ($page -1)) . ',' . $page_size;
    	}
    	$this->freeQuery($query);
    	$resultado_paginado->setResults($this->result);
    	$resultado_paginado->setPage($page);
    	$this->result = $resultado_paginado;
    	return true;
    }
    
    public function count_free_select($query){
    	$pos = strripos($query, 'from');
    	if($pos){
    		$query = 'SELECT count(*) as CNT '.substr($query, $pos);
    		$this->freeQuery($query);
    	}
    }
    
	
    /*
    * Inserta valor en tabla
    * Required: table (nombre de la tabla)
    *           values (valores a insertar)
    * Optional: rows 
    */
    public function insert($table,$values,$rows = null, $onduplicate = false)
    {
            $insert = 'INSERT INTO '.$table;
            if($rows != null)
            {
				$rows_string = implode(',',$rows);
            	$insert .= ' ('.$rows_string.')';
            }
            for($i = 0; $i < count($values); $i++)
            {
                if(is_string($values[$i]))
                    $values[$i] = '"'.$values[$i].'"';
            }
            $values = implode(',',$values);
            $insert .= ' VALUES ('.$values.')';
            
            if(!empty($rows) and $onduplicate){
            	$updates = array();
            	for($i = 0; $i < count($rows); $i++){
            		$updates[$i] = $rows[$i] . ' = VALUES (`'.$rows[$i].'`)';
            	}
            	$updates = implode(',',$updates);
            	$insert .= ' ON DUPLICATE KEY UPDATE  '. $updates;
            }
            
            $ins = mysql_query($insert);

            if($ins)
            {
                return true;
            }
            else
            {
                return false;
            }
        
    }

    
  public function insert_update($obj, $mapping)
    {
            $insert = 'INSERT INTO '.$obj->getNombreTabla();
            
		    $reflect = new ReflectionClass($obj);
			$rows   = ReflectUtils::getAllproperties(get_class($obj)) ;
            
            
            $columns_name = array();
			$values = array();
           	$updates = array();
			for($i = 0; $i < count($rows); $i++){
				$prop_name =  $rows[$i]->getName();
				$columns_name[$i] = $mapping[$prop_name];
				$getter = ReflectUtils::getter($prop_name);
				$values[$i] = $this->get_sql_vaue($obj->$getter());
				$updates[$i] = $columns_name[$i]. ' = VALUES (`'.$columns_name[$i].'`)';
		   	}
			   
			
			$columns_name = implode(',',$columns_name);
          	$values = implode(',',$values);
            $updates = implode(',',$updates);
            
          	$insert .= ' ('.$columns_name.')';
            $insert .= ' VALUES ('.$values.')';
            $insert .= ' ON DUPLICATE KEY UPDATE  '. $updates;
            
            
            $ins = @mysql_query($insert);

            if($ins)
            {
                return true;
            }
            else
            {
                return false;
            }
        
    }
    
    private function get_sql_vaue($value){
    	if(is_string($value))
			return '"'.mysql_real_escape_string($value).'"';
		return $value;
    }
    /*
    * Elimina tabla o registros 
    * Required: table 
    * Optional: where (condition [columna =  valor])
    */
    public function delete($table,$where = null)
    {
     
        
		$delete =  $where == null? $delete = 'DELETE '.$table : 'DELETE FROM '.$table.' WHERE '.$where;
        
		return @mysql_query($delete);
    }

    /*
     * Realiza update de la tabla se un valor dado
     * Required: table 
     *           rows 
     *           where
     */
    public function update($table,$rows,$where)
    {
          // Parse the where values
			// even values (including 0) contain the where rows
			// odd values contain the clauses for the row
			for($i = 0; $i < count($where); $i++)
			{
			if($i%2 != 0)
			{
			if(is_string($where[$i]))
			{
			if(!empty($where[($i+1)]))
			$where[$i] = "='".$where[$i]."' AND ";
			else
			$where[$i] = "='".$where[$i]."'";
			}
			}
			}
			$where = implode('',$where);
			$update = 'UPDATE '.$table.' SET ';
			$keys = array_keys($rows);
            for($i = 0; $i < count($rows); $i++)
            {
                if(is_string($rows[$keys[$i]]))
                {
                    $update .= $keys[$i].'="'.$rows[$keys[$i]].'"';
                }
                else
                {
                    $update .= $keys[$i].'='.$rows[$keys[$i]];
                }

                // parsea para agregar comas
                if($i != count($rows)-1)
                {
                    $update .= ',';
                }
            }
            $update .= ' WHERE '.$where;
            return @mysql_query($update);          
        
    }

    /*
    * devuelve el conjunto de resultados
    */
    public function getResult()
    {
        return $this->result;
    }
    
    /*
     * Devuelve un resultado unico
     */
    public function getUniqueResult(){
    	
    	return $this->numResults == 1 ? $this->result[0] :false; 
    }
    
    /*
     * Si existe conexion la cierra.
     */
    public function disconnect()
    {
    	if($this->con)
    	{
    		if(@mysql_close())
    		{
    			$this->con = false;
    			return true;
    		}
    		else
    		{
    			return false;
    		}
    	}
    }
    
    /* Hacer una consulta libre*/
    public function freeQuery($query){
    	$this->cleanResults();
    	$queryResult = @mysql_query($query);
    	if($queryResult)
    	{
    		$this->numResults = mysql_num_rows($queryResult);
    		for($i =0;  $row = mysql_fetch_assoc($queryResult);$i++) {
    			$this->result[$i] = $row;
    		}
    	
    		return true;
    	}
    	else
    	{
    		return false;
    	}
    }
    
    public function cleanResults(){
    	$this->result = array();
    }
}

?>